﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Agresso.Interface.Layer.SQL
{
    public class VoyageMasterSQL
    {
        public static string GetAllVoyageMaster()
        {
            return @"
           Select distinct 
                    vVMVoyageSrch.VesselName vessel_name,
                    vVMVoyageSrch.Vessel vessel_code,
                    vDeptOrganisationSrch.DeptName dept_fullname,
                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre_fullname,
                    vVMVoyageSrch.VoyageNo voyage_no,
                    vVMOpfixSrch.CodirCharterer charterer,
                    vVMOpfixSrch.FixtureType head_fixture_type,
                    vVMOpfixSrch.ContractDate cp_date,
                    vVMVoyageSrch.EstStartDate estimated_start_date,
                    vVMVoyageSrch.StartBallast start_ballast_date,
                    vVMVoyageSrch.StartVoyageDate start_voyage_delivery_date,
                    vVMVoyageSrch.EndVoyageDate end_voyage_delivery_date,
                    vVMVoyageSrch.TotalOnHireDays days_total_on_hire,
                    vVMVoyageOffHirePeriodSrch.Duration days_total_off_hire,
                    vVMVoyageTCESrch.TCE tce,
                    vVMVoyCargoCommodityAltSrch.CargoType commodity_type,
                    --vVMFixtureSrch.LDQuantity cargo_quantity,
                    vOpchargeFixtureSrch.LoadedCargoQuantity cargo_quantity,
                    vVMVoyageSrch.Status status,
                    vVMVoyageSrch.HeadFixture head_fixture_number,
                    vVMOpfixSrch.OpsReference fixture_ref,
                    vVMOpfixSrch.OurRef head_fixture_ref,
                    FreightRateBasis freight_rate_basis,
                    OperatedBy operator_m,
                    Admin admin,
                    vVMVoyageSrch.Reference voyage_ref,
                    vVMVoyageSrch.Created created_date,
                    Rate,
                    Commodity commodity,
                    vVMVoyageSrch.LastUpdatedDate last_updated_date,
                     vOpchargeFixtureSrch.Charterer charterer_
                     FROM VVMVOYAGESRCH 
                        LEFT OUTER JOIN VVMFIXTURESRCH ON VVMVOYAGESRCH.ID=VVMFIXTURESRCH.VMVOYAGENO 
                     LEFT OUTER JOIN VVMVOYCARGOSRCH ON VVMVOYAGESRCH.ID = VVMVOYCARGOSRCH.VOYID 
                     LEFT OUTER JOIN VVMOPFIXSRCH ON VVMVOYAGESRCH.ID = VVMOPFIXSRCH.VMVOYAGENO AND VVMVOYAGESRCH.ID=VVMOPFIXSRCH.VMVOYAGENO AND VVMOPFIXSRCH.OPSFIXTURE=VVMFIXTURESRCH.OPSFIXTURE 
                     LEFT OUTER JOIN VVMVOYAGETCESRCH ON VVMVOYAGESRCH.ID=VVMVOYAGETCESRCH.VMVOYAGE 
                     LEFT OUTER JOIN VDEPTORGANISATIONSRCH ON VVMVOYAGESRCH.ID=VDEPTORGANISATIONSRCH.ID AND VVMVOYAGESRCH.ID = VDEPTORGANISATIONSRCH.ID 
                     LEFT OUTER JOIN VVMVOYAGEPORTSRCH ON VVMVOYAGEPORTSRCH.ID=VVMFIXTURESRCH.VMVOYAGEPORT AND VVMFIXTURESRCH.VMVOYAGEPORT=VVMVOYAGEPORTSRCH.ID 
                     LEFT OUTER JOIN VVMVOYCARGOCOMMODITYALTSRCH ON VVMVOYCARGOCOMMODITYALTSRCH.CARGO = VVMVOYCARGOSRCH.COMMODITY 
                     LEFT OUTER JOIN VPROFITCENTREORGANISATIONSRCH ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID 
                     LEFT OUTER JOIN VVMVOYAGEOFFHIREPERIODSRCH ON VVMVOYAGESRCH.ID = VVMVOYAGEOFFHIREPERIODSRCH.VOYAGE 
                     LEFT OUTER JOIN VOPCHARGEFIXTURESRCH ON VVMOPFIXSRCH.OPSFIXTURE = VOPCHARGEFIXTURESRCH.FIXTURE AND 
								 VVMOPFIXSRCH.OPSOPTIONNO = VOPCHARGEFIXTURESRCH.OPSOPTIONNO AND 
								 VVMOPFIXSRCH.OPSVOYAGENO = VOPCHARGEFIXTURESRCH.OPSVOYAGENO 
                     WHERE 
                     vProfitCentreOrganisationSrch.ProfitCentreName <> 'PRAEWA PIPATYOTHAGUL' and
                     vVMVoyageSrch.Category NOT LIKE 'UnFixed' AND 
                     --(vVMVoyageSrch.Status <> 'Voyage Completed') AND 
                     (vVMVoyageSrch.Status <> 'Cancelled') and
                     vVMVoyageSrch.StartVoyageDate > '2013-07-23 00:00'
                     --AND vVMVoyageSrch.Created > '2012-01-01 00:00'
                     --AND vVMVoyageSrch.VesselName like '%Glorious Sunrise%'
                     --AND vVMVoyageSrch.VoyageNo = 26
                     ORDER BY vVMVoyageSrch.Created
                     --ORDER BY days_total_off_hire desc
            ";
        }
        public static string GetAllVoyageMasterByVesselNameVoyage()
        {
            return @"
           Select distinct 
                    vVMVoyageSrch.VesselName vessel_name,
                    vDeptOrganisationSrch.DeptName dept_fullname,
                    vProfitCentreOrganisationSrch.ProfitCentreName profit_centre_fullname,
                    vVMVoyageSrch.VoyageNo voyage_no,
                    vVMOpfixSrch.CodirCharterer charterer,
                    vVMOpfixSrch.FixtureType head_fixture_type,
                    vVMOpfixSrch.ContractDate cp_date,
                    vVMVoyageSrch.EstStartDate estimated_start_date,
                    vVMVoyageSrch.StartBallast start_ballast_date,
                    vVMVoyageSrch.StartVoyageDate start_voyage_delivery_date,
                    vVMVoyageSrch.EndVoyageDate end_voyage_delivery_date,
                    vVMVoyageSrch.TotalOnHireDays days_total_on_hire,
                    vVMVoyageOffHirePeriodSrch.Duration days_total_off_hire,
                    vVMVoyageTCESrch.TCE tce,
                    vVMVoyCargoCommodityAltSrch.CargoType commodity_type,
                    --vVMFixtureSrch.LDQuantity cargo_quantity,
                    vOpchargeFixtureSrch.LoadedCargoQuantity cargo_quantity,
                    vVMVoyageSrch.Status status,
                    vVMVoyageSrch.HeadFixture head_fixture_number,
                    vVMOpfixSrch.OpsReference fixture_ref,
                    vVMOpfixSrch.OurRef head_fixture_ref,
                    FreightRateBasis freight_rate_basis,
                    OperatedBy operator_m,
                    Admin admin,
                    vVMVoyageSrch.Reference voyage_ref,
                    vVMVoyageSrch.Created created_date,
                    Rate,
                    Commodity commodity,
                    vVMVoyageSrch.LastUpdatedDate last_updated_date,
                     vOpchargeFixtureSrch.Charterer charterer_
                     FROM VVMVOYAGESRCH 
                        LEFT OUTER JOIN VVMFIXTURESRCH ON VVMVOYAGESRCH.ID=VVMFIXTURESRCH.VMVOYAGENO 
                     LEFT OUTER JOIN VVMVOYCARGOSRCH ON VVMVOYAGESRCH.ID = VVMVOYCARGOSRCH.VOYID 
                     LEFT OUTER JOIN VVMOPFIXSRCH ON VVMVOYAGESRCH.ID = VVMOPFIXSRCH.VMVOYAGENO AND VVMVOYAGESRCH.ID=VVMOPFIXSRCH.VMVOYAGENO AND VVMOPFIXSRCH.OPSFIXTURE=VVMFIXTURESRCH.OPSFIXTURE 
                     LEFT OUTER JOIN VVMVOYAGETCESRCH ON VVMVOYAGESRCH.ID=VVMVOYAGETCESRCH.VMVOYAGE 
                     LEFT OUTER JOIN VDEPTORGANISATIONSRCH ON VVMVOYAGESRCH.ID=VDEPTORGANISATIONSRCH.ID AND VVMVOYAGESRCH.ID = VDEPTORGANISATIONSRCH.ID 
                     LEFT OUTER JOIN VVMVOYAGEPORTSRCH ON VVMVOYAGEPORTSRCH.ID=VVMFIXTURESRCH.VMVOYAGEPORT AND VVMFIXTURESRCH.VMVOYAGEPORT=VVMVOYAGEPORTSRCH.ID 
                     LEFT OUTER JOIN VVMVOYCARGOCOMMODITYALTSRCH ON VVMVOYCARGOCOMMODITYALTSRCH.CARGO = VVMVOYCARGOSRCH.COMMODITY 
                     LEFT OUTER JOIN VPROFITCENTREORGANISATIONSRCH ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID 
                     LEFT OUTER JOIN VVMVOYAGEOFFHIREPERIODSRCH ON VVMVOYAGESRCH.ID = VVMVOYAGEOFFHIREPERIODSRCH.VOYAGE 
                     LEFT OUTER JOIN VOPCHARGEFIXTURESRCH ON VVMOPFIXSRCH.OPSFIXTURE = VOPCHARGEFIXTURESRCH.FIXTURE AND 
								 VVMOPFIXSRCH.OPSOPTIONNO = VOPCHARGEFIXTURESRCH.OPSOPTIONNO AND 
								 VVMOPFIXSRCH.OPSVOYAGENO = VOPCHARGEFIXTURESRCH.OPSVOYAGENO 
                     WHERE 
                     vProfitCentreOrganisationSrch.ProfitCentreName <> 'PRAEWA PIPATYOTHAGUL' and
                     --vVMVoyageSrch.Category NOT LIKE 'UnFixed' AND 
                     --(vVMVoyageSrch.Status <> 'Voyage Completed') AND 
                     --(vVMVoyageSrch.Status <> 'Cancelled') and
                     --vVMVoyageSrch.StartVoyageDate > '2013-07-23 00:00'
                     --AND vVMVoyageSrch.Created > '2012-01-01 00:00'
                     vVMVoyageSrch.VesselName = @VesselName
                     AND vVMVoyageSrch.VoyageNo = @VoyageNo
                     and vOpchargeFixtureSrch.LoadedCargoQuantity > 0
                     ORDER BY vVMVoyageSrch.Created
                     --ORDER BY days_total_off_hire desc
            ";
        }

        public static string CountVoyageMasterTemp()
        {
            return @"select count(*) from i_voyage_master 
            where
            [vessel_name] = @vessel_name
      and [dept_fullname] = @dept_fullname
      and [voyage_no] = @voyage_no
      and [charterer] = @charterer
      and [head_fixture_type] = @head_fixture_type
      and [cp_date] = @cp_date
      and [estimated_start_date] = @estimated_start_date
      and [start_ballast_date] = @start_ballast_date
      and [start_voyage_delivery_date] = @start_voyage_delivery_date
      and [end_voyage_delivery_date] = @end_voyage_delivery_date
      and [days_total_on_hire] = @days_total_on_hire
      and [days_total_off_hire] = @days_total_off_hire
      and [tce] = @tce
      and [commodity_type] = @commodity_type
      and [cargo_quantity] = @cargo_quantity
      and [status] = @status
      and [head_fixture_number] = @head_fixture_number
      and [fixture_ref] = @fixture_ref
      and [head_fixture_ref] = @head_fixture_ref
      and [freight_rate_basis] = @freight_rate_basis
      and [operator_m] = @operator_m
      and [admin] = @admin
      and [voyage_ref] = @voyage_ref
      and [created_date] = @created_date   
      and [profit_centre_fullname] = @profit_centre_fullname
      and [Rate] = @Rate
        and [commodity] =@commodity
        and [last_updated_date] = @last_updated_date
        and [charterer_] =@charterer_
        --and [vessel_code] = @vessel_code
            ";
        }

        public static string InsertVoyageMaster()
        {
            return @"
            INSERT INTO i_voyage_master
           (pickup_flag
            ,insert_date
            ,[vessel_name]
           ,[dept_fullname]
           ,[voyage_no]
           ,[charterer]
           ,[head_fixture_type]
           ,[cp_date]
           ,[estimated_start_date]
           ,[start_ballast_date]
           ,[start_voyage_delivery_date]
           ,[end_voyage_delivery_date]
           ,[days_total_on_hire]
           ,[days_total_off_hire]
           ,[tce]
           ,[commodity_type]
           ,[cargo_quantity]
           ,[status]
           ,[head_fixture_number]
           ,[fixture_ref]
           ,[head_fixture_ref]
           ,[freight_rate_basis]
           ,[operator_m]
           ,[admin]
           ,[voyage_ref]
           ,[created_date]
            ,[profit_centre_fullname]
            ,[Rate]
            ,[commodity]         
            ,[last_updated_date] 
            ,[charterer_]
            ,[vessel_code]
             )
     VALUES
           (@pickup_flag
            ,@insert_date
            ,@vessel_name
           ,@dept_fullname
           ,@voyage_no
           ,@charterer
           ,@head_fixture_type
           ,@cp_date
           ,@estimated_start_date
           ,@start_ballast_date
           ,@start_voyage_delivery_date
           ,@end_voyage_delivery_date
           ,@days_total_on_hire
           ,@days_total_off_hire
           ,@tce
           ,@commodity_type
           ,@cargo_quantity
           ,@status
           ,@head_fixture_number
           ,@fixture_ref
           ,@head_fixture_ref
           ,@freight_rate_basis
           ,@operator_m
           ,@admin
           ,@voyage_ref
           ,@created_date
            ,@profit_centre_fullname
            ,@Rate
            ,@commodity          
            ,@last_updated_date
            ,@charterer_
            ,@vessel_code
            )
            ";
        }

        public static string GetAllVoyageMasterOutstandingInterface()
        {
            return "select * from i_voyage_master where pickup_flag='0'";
        }

        public static string UpdateFlag()
        {
            return @"
            update i_voyage_master
            set pickup_flag = @pickup_flag
            ,pickup_date = @pickup_date
            ,pickup_update_date = @pickup_update_date
            ,remarks = @remarks
            WHERE i_voyage_master_id = @i_voyage_master_id
            ";
        }

        public static string GetAllPendingVoyageMaster(string p)
        {
            return string.Format(@"select distinct(voyage_no), vessel_name,  created_date, convert(char, pickup_date, 101) pickup_date, remarks from i_voyage_master where pickup_flag = 0
                and convert(char,voyage_no)+vessel_name+  convert(char,created_date, 106)+ replace(convert(char(11), pickup_date, 106),' ','-')+ remarks like '%{0}%'
                order by created_date", p);
        }

        public static string GetVoyageMasterByVesselNameAndVoyageAndProfitCentre()
        {
            return @"select * from i_voyage_master where vessel_name=@vessel_name and voyage_no=@voyage_no and profit_centre_fullname=@profit_centre_fullname order by insert_date desc";
        }

        public static string GetAllPendingVoyageMaster()
        {
            return @"select distinct(voyage_no), vessel_name,  created_date, convert(char, pickup_date, 101) pickup_date, remarks from i_voyage_master where pickup_flag = 0  order by created_date";
        }

        public static string GetAllPendingVoyageMasterCount()
        {
            return @"select count(*) from i_voyage_master where pickup_flag = 0";
        }
    }
}
